# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql

class XpcPipeline(object):

    def open_spider(self, spider):
        self.db = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root',
            database='xpc_1809',  # 数据库名
            charset='utf8mb4'  # 编码：utf8mb4比utf8更大(占4个字节)的编码
        )
        self.cursor = self.db.cursor()
        self.i = 0

    # 每个yield的item都会进入这里
    def process_item(self, item, spider):

        # 计数
        # self.i += 1
        # print("第 %d 条..." % self.i)


        # 通过zip，把item中所有的key和value分别取出，而且顺序一一对应
        # print(item)
        # print(item.items())
        # print(*item.items(), "\n")

        keys, values = zip(*item.items())
        # print(keys)  # 所有的字段
        # ('pid', 'thumbnail', 'title', ...)
        # print(values)  # 所有的值
        # ('10339921', '1c.jpg', '陳冠希我聽我聲微')

        sql = "insert into `{}`({}) values({}) on duplicate key update {}".format(
            item.table_name,  # 表名
            ','.join(['`%s`'%key for key in keys]),  # 所有字段
            ','.join(['%s'] * len(values)),  # %s
            ','.join(['`{}`=%s'.format(key) for key in keys])
        )
        # print(sql)
        # print(sql % values)

        # print()
        # print("1:", ','.join(['`%s`'%key for key in keys]))
        # print("2:", ','.join(['%s'] * len(values)))
        # print("3:", ','.join(['{}=%s'.format(key) for key in keys]))
        # print("4sql:", sql)
        '''
        1: `pid`,`thumbnail`,`title`
        2: %s,%s,%s
        3: pid=%s,thumbnail=%s,title=%s
        4sql: insert into `posts`(pid,thumbnail,title) values(%s,%s,%s) on duplicate key update pid=%s,thumbnail=%s,title=%s
        '''

        '''
        sql = "insert into posts(pid, title, thumbnail) values(%r,%r,%r)" % (pid, title, thumbnail)'''

        # 执行sql
        self.cursor.execute(sql, values*2)
        self.db.commit()

        return item


    def close_spider(self, spider):
        self.cursor.close()
        self.db.close()

